package mall.dao;

import mall.model.Goods;
import mall.model.Spec;
import mall.model.Type;
import mall.model.bo.AddSpecBO;
import mall.model.bo.ReplyBO;
import mall.model.bo.UpdateGoodsBO;
import mall.model.vo.*;
import mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/*
 *
 * @author Yonni
 * @version 1.8.0_201
 * @date 2020-05-28 21:07
 *
 * */
public class GoodsDaoImpl implements GoodsDao {
    public List<Type> getType() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Type> typeList = null;
        try {
            typeList = queryRunner.query("select * from type", new BeanListHandler<Type>(Type.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return typeList;
    }

    public List<TypeGoodsVO> getGoodsByType(String typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<TypeGoodsVO> goodsVOList = null;
        try {
            goodsVOList = queryRunner.query("select id, img, name, price, typeId, stockNum from goods where typeId = ?",
                    new BeanListHandler<TypeGoodsVO>(TypeGoodsVO.class),
                    typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsVOList;
    }

    @Override
    public void addGoods(Goods goods) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            //返回值表示影响的行数
            queryRunner.update("insert into goods values (null,?,?,?,?,?,?)",
                    goods.getImg(),
                    goods.getName(),
                    goods.getPrice(),
                    goods.getTypeId(),
                    goods.getStockNum(),
                    goods.getDesc());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int lastInsertId() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //last_insert_id()，获取当前线程下最近一次插入数据的id，不需要from goods
        BigInteger query = null;
        try {
            query = (BigInteger) queryRunner.query("select last_insert_id()", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.intValue();
    }

    @Override
    public void addSpecs(List<Spec> specs) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec : specs) {
            try {
                queryRunner.update("insert into spec values (null,?,?,?,?)",
                        spec.getSpecName(),
                        spec.getStockNum(),
                        spec.getUnitPrice(),
                        spec.getGoodsId());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void addType(Type type) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into type values (null,?)", type.getName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public GoodsInfoVO goodsInfoVO(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        GoodsInfoVO query = null;
        try {
            query = queryRunner.query("select id, img, name, `desc`, typeId, price as unitPrice from goods where id = ?",
                    new BeanHandler<GoodsInfoVO>(GoodsInfoVO.class),
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<SpecsInfoVO> specsInfoVO(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<SpecsInfoVO> query = null;
        try {
            query = queryRunner.query("select id, specName, stockNum, unitPrice from spec where goodsId = ?",
                    new BeanListHandler<SpecsInfoVO>(SpecsInfoVO.class),
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public void deleteGoods(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteSpec(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from spec where goodsId = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<NoReplyMsgVO> noReplyMsg() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<NoReplyMsgVO> query = null;
        try {
            query = queryRunner.query("select id, userId, goodsId, content, state, createtime, goodsName, nickname from contents where state = 1",
                    new BeanListHandler<NoReplyMsgVO>(NoReplyMsgVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query;
    }

    @Override
    public List<RepliedMsgVO> repliedMsg() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<RepliedMsgVO> query = null;
        try {
            query = queryRunner.query("select id, userId, goodsId, content, replyContent, state, createtime, goodsName, nickname from contents where state = 0",
                    new BeanListHandler<RepliedMsgVO>(RepliedMsgVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public void reply(ReplyBO replyBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //获取当前时间
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String curDate = dateFormat.format(date);
        try {
            queryRunner.update("update contents set replyContent = ?, state = ?, replyCreatetime = ? where id = ?",
                    replyBO.getContent(), 0, curDate, replyBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addSpec(AddSpecBO addSpecBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into spec values (null,?,?,?,?)",
                    addSpecBO.getSpecName(),
                    addSpecBO.getStockNum(),
                    addSpecBO.getUnitPrice(),
                    addSpecBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateGoods(UpdateGoodsBO updateGoodsBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update goods set `desc` = ?, img = ?, name = ?, typeId = ? where id = ?",
                    updateGoodsBO.getDesc(), updateGoodsBO.getImg(), updateGoodsBO.getName(),
                    updateGoodsBO.getTypeId(), updateGoodsBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
